package data.services.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

import data.objects.User;
import data.services.UserServices;
import data.utils.DataConnector;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class UserServicesImpl implements UserServices {
    private DataConnector dataConnection = new DataConnector();

    @Override
    public User getUserNameById(String userId) {
        User user = null;
        final String QUERY = "SELECT * FROM USERS WHERE id = ?";
        final Connection connection = dataConnection.getConnection();
        CallableStatement callableStatement = null;
        ResultSet resultSet = null;
        try {
                callableStatement = connection.prepareCall(QUERY);
                callableStatement.setString(1, userId);
                resultSet = callableStatement.executeQuery();
                while (resultSet.next()) {
                    user = new User(
                        resultSet.getString("id"),
                        resultSet.getString("username"),
                        resultSet.getString("password"),
                        resultSet.getString("firstname"),
                        resultSet.getString("lastname"),
                        resultSet.getString("email"),
                        resultSet.getString("phone"),
                        resultSet.getString("address"),
                        resultSet.getString("birthday"),
                        resultSet.getString("role")
                    );
                }
                resultSet.close();
                callableStatement.close();
                connection.close();
        } catch (SQLException e) {
                e.printStackTrace();
        }
        return user;
    }

    @Override
    public boolean isExistUser(String userName, String password) {
        boolean result = false;
        final String QUERY = "SELECT * FROM USERS WHERE username = ? AND password = ?";
        final Connection connection = dataConnection.getConnection();
        CallableStatement callableStatement = null;
        ResultSet resultSet = null;
        try {
                callableStatement = connection.prepareCall(QUERY);
                callableStatement.setString(1, userName);
                callableStatement.setString(2, password);
                resultSet = callableStatement.executeQuery();
                if (resultSet.next())
                        result = true;
                resultSet.close();
                callableStatement.close();
                connection.close();
        } catch (SQLException e) {
                e.printStackTrace();
        }
        return result;
    }

    @Override
    public boolean isAdminUser(String userName, String password) {
        boolean result = false;
        final String QUERY = "SELECT * FROM USERS WHERE username = ? AND password = ?";
        final Connection connection = dataConnection.getConnection();
        CallableStatement callableStatement = null;
        ResultSet resultSet = null;
        try {
            callableStatement = connection.prepareCall(QUERY);
            callableStatement.setString(1, userName);
            callableStatement.setString(2, password);
            resultSet = callableStatement.executeQuery();
            if (resultSet.next()) {
                if(resultSet.getString("role").equals("1"))
                    result = true;
            }
            resultSet.close();
            callableStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public List<User> getAllUserInfo() {
        List<User> userList = new ArrayList<User>();
        final String QUERY = "SELECT * FROM USERS";
        final Connection connection = dataConnection.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(QUERY);
            while (resultSet.next()) {
                User user = new User(
                    resultSet.getString("id"),
                    resultSet.getString("username"),
                    resultSet.getString("password"),
                    resultSet.getString("firstname"),
                    resultSet.getString("lastname"),
                    resultSet.getString("email"),
                    resultSet.getString("phone"),
                    resultSet.getString("address"),
                    resultSet.getString("birthday"),
                    resultSet.getString("role")
                );
                userList.add(user);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage().toString());
        } finally {
        	try {
				resultSet.close();
				statement.close();
	        	connection.close();
			} catch (SQLException e) {
				System.err.println(e.getMessage().toString());
			}	
        }
        return userList;
    }

    public static void main(String[] args) throws SQLException {
        UserServicesImpl us = new UserServicesImpl();
        User user = us.getUserNameById("1");
        if(user == null) {
                System.out.println("not found");
        } else {
                System.out.println(user.getFirstName() + " " + user.getLastName());
        }

        System.out.println(us.isExistUser("thattu", "123456"));
        System.out.println(us.getAllUserInfo().size());
    }

	@Override
	public boolean insertNewUser(User user) {
		final String QUERY = "INSERT INTO USERS VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		final Connection connection = dataConnection.getConnection();
        CallableStatement callableStatement = null;
        
        try {
        	 callableStatement = connection.prepareCall(QUERY);
        	 callableStatement.setString(1, user.getId());
        	 callableStatement.setString(2, user.getUserName());
        	 callableStatement.setString(3, user.getPassword());
        	 callableStatement.setString(4, user.getFirstName());
        	 callableStatement.setString(5, user.getLastName());
        	 callableStatement.setString(6, user.getEmail());
        	 callableStatement.setString(7, user.getPhone());
        	 callableStatement.setString(8, user.getAddress());
        	 callableStatement.setDate(9, Date.valueOf(user.getBirthday()));
        	 callableStatement.setInt(10, Integer.valueOf(user.getRole()));
        	 callableStatement.executeQuery();
        	 
        	 callableStatement.close();
        	 connection.close();
        } catch (SQLException sqlE) {
        	System.out.println(sqlE.getMessage().toString());
        }
		return false;
	}
}
